#!/bin/bash

# Pincaicai 2.0 Database Initialization Script
# This script executes all SQL files in sequence to create database, tables, constraints, functions and triggers
#
# Database configuration notes:
# 1. Default configuration is defined in app/utils/database.py
# 2. Can be overridden by environment variables
# 3. To modify database connection info, please edit app/utils/database.py file
# 4. Or set environment variables: export DB_HOST=your_host DB_PORT=your_port etc.

# Set color output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Automatically read database configuration from database.py
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"

# Use Python to read configuration from database.py
eval $(python3 -c "
import sys
sys.path.append('$PROJECT_ROOT')
from app.utils.database import DB_CONFIG
print(f'DB_HOST={DB_CONFIG[\"host\"]}')
print(f'DB_PORT={DB_CONFIG[\"port\"]}')
print(f'DB_NAME={DB_CONFIG[\"database\"]}')
print(f'DB_USER={DB_CONFIG[\"user\"]}')
print(f'DB_PASSWORD={DB_CONFIG[\"password\"]}')
")

echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}    Pincaicai 2.0 Database Init Script${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""

# Check if PostgreSQL is available
echo -e "${YELLOW}Checking PostgreSQL connection...${NC}"
if ! command -v psql &> /dev/null; then
    echo -e "${RED}Error: psql command not found. Please ensure PostgreSQL client is installed.${NC}"
    exit 1
fi

# Test database connection
export PGPASSWORD="$DB_PASSWORD"
if ! psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -c "SELECT 1;" &> /dev/null; then
    echo -e "${RED}Error: Cannot connect to PostgreSQL server${NC}"
    echo -e "${RED}Please check connection parameters: Host=$DB_HOST, Port=$DB_PORT, User=$DB_USER${NC}"
    exit 1
fi

echo -e "${GREEN}✓ PostgreSQL connection successful${NC}"
echo ""

# Create database (if it doesn't exist)
echo -e "${YELLOW}Checking if database exists...${NC}"
if psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -lqt | cut -d \| -f 1 | grep -qw "$DB_NAME"; then
    echo -e "${YELLOW}Database '$DB_NAME' already exists${NC}"
    read -p "Do you want to recreate the database? This will delete all existing data! (y/N): " -n 1 -r
    echo
    if [[ $REPLY =~ ^[Yy]$ ]]; then
        echo -e "${YELLOW}Deleting existing database...${NC}"
        psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -c "DROP DATABASE IF EXISTS $DB_NAME;"
        echo -e "${GREEN}✓ Database deleted${NC}"
        echo -e "${YELLOW}Recreating database '$DB_NAME'...${NC}"
        psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -c "CREATE DATABASE $DB_NAME;"
        echo -e "${GREEN}✓ Database recreated successfully${NC}"
    else
        echo -e "${YELLOW}Using existing database to continue...${NC}"
    fi
else
    echo -e "${YELLOW}Creating database '$DB_NAME'...${NC}"
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -c "CREATE DATABASE $DB_NAME;"
    echo -e "${GREEN}✓ Database created successfully${NC}"
fi

echo ""

# Define SQL file execution order
SQL_FILES=(
    "$SCRIPT_DIR/01_create_tables.sql"
    "$SCRIPT_DIR/02_insert_sample_data.sql"
    "$SCRIPT_DIR/03_create_constraints.sql"
    "$SCRIPT_DIR/04_create_triggers_functions.sql"
    "$SCRIPT_DIR/05_update_triggers_for_inventory.sql"
    "$SCRIPT_DIR/06_complex_queries.sql"
)

# Execute SQL files
echo -e "${BLUE}Starting SQL file execution...${NC}"
echo ""

for sql_file in "${SQL_FILES[@]}"; do
    if [ -f "$sql_file" ]; then
        echo -e "${YELLOW}Executing: $sql_file${NC}"
        
        # Execute SQL file
        if psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -f "$sql_file" -v ON_ERROR_STOP=1; then
            echo -e "${GREEN}✓ $sql_file executed successfully${NC}"
        else
            echo -e "${RED}✗ $sql_file execution failed${NC}"
            echo -e "${RED}Script execution terminated${NC}"
            exit 1
        fi
        echo ""
    else
        echo -e "${RED}Warning: File $sql_file does not exist, skipping${NC}"
    fi
done

echo -e "${BLUE}========================================${NC}"
echo -e "${GREEN}Database initialization completed!${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""
echo -e "${YELLOW}Database information:${NC}"
echo -e "  Database name: ${GREEN}$DB_NAME${NC}"
echo -e "  Host: ${GREEN}$DB_HOST${NC}"
echo -e "  Port: ${GREEN}$DB_PORT${NC}"
echo -e "  User: ${GREEN}$DB_USER${NC}"
echo ""
echo -e "${YELLOW}Created tables:${NC}"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "\dt" 2>/dev/null | grep -v "List of relations" | grep -v "Schema" | grep -v "Name" | grep -v "Type" | grep -v "Owner" | grep -v "----" | grep -v "^$" | sed 's/^/  /'
echo ""
echo -e "${YELLOW}Created views:${NC}"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "\dv" 2>/dev/null | grep -v "List of relations" | grep -v "Schema" | grep -v "Name" | grep -v "Type" | grep -v "Owner" | grep -v "----" | grep -v "^$" | sed 's/^/  /'
echo ""
echo -e "${GREEN}Database initialization script execution completed!${NC}"
